#!/bin/bash

# 脚本参数说明：
#   第1个参数$1，表示日志类型，指定同步DWD层某个日志表数据
#   第2个参数$2，表示日期，同步某一天数据，从ODS层提取加载到DWD层

APP=gmall

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else
    do_date=`date -d "-1 day" +%F`
fi

# DWD层启动start日志加载脚本
dwd_start_log="
SET hive.stats.column.autogather = false ;
INSERT OVERWRITE TABLE ${APP}.dwd_start_log PARTITION(dt = '${do_date}')
SELECT
    get_json_object(log, '$.common.ar') AS area_code,
    get_json_object(log, '$.common.ba') AS brand,
    get_json_object(log, '$.common.ch') AS channel,
    get_json_object(log, '$.common.is_new') AS is_new,
    get_json_object(log, '$.common.md') AS model,
    get_json_object(log, '$.common.mid') AS mid_id,
    get_json_object(log, '$.common.os') AS os,
    get_json_object(log, '$.common.uid') AS user_id,
    get_json_object(log, '$.common.vc') AS version_code,
    get_json_object(log, '$.start.entry') AS entry,
    get_json_object(log, '$.start.loading_time') AS loading_time,
    get_json_object(log, '$.start.open_ad_id') AS open_ad_id,
    get_json_object(log, '$.start.open_ad_ms') AS open_ad_ms,
    get_json_object(log, '$.start.open_ad_skip_ms') AS open_ad_skip_ms,
    get_json_object(log, '$.ts') AS ts
FROM ${APP}.ods_log
WHERE dt = '${do_date}'
  AND get_json_object(log, '$.start') IS NOT NULL;
"

# DWD层页面page日志加载脚本
dwd_page_log="
SET hive.stats.column.autogather = false ;
INSERT OVERWRITE TABLE ${APP}.dwd_page_log PARTITION(dt='${do_date}')
SELECT
    get_json_object(log, '$.common.ar') AS area_code,
    get_json_object(log, '$.common.ba') AS brand,
    get_json_object(log, '$.common.ch') AS channel,
    get_json_object(log, '$.common.is_new') AS is_new,
    get_json_object(log, '$.common.md') AS model,
    get_json_object(log, '$.common.mid') AS mid_id,
    get_json_object(log, '$.common.os') AS os,
    get_json_object(log, '$.common.uid') AS user_id,
    get_json_object(log, '$.common.vc') AS version_code,
    get_json_object(log, '$.page.during_time') AS during_time,
    get_json_object(log, '$.page.item') AS page_item,
    get_json_object(log, '$.page.item_type') AS page_item_type,
    get_json_object(log, '$.page.last_page_id') AS last_page_id,
    get_json_object(log, '$.page.page_id') AS page_id,
    get_json_object(log, '$.page.source_type') AS source_type,
    get_json_object(log, '$.ts') AS ts
FROM ${APP}.ods_log
WHERE dt = '${do_date}'
  AND get_json_object(log, '$.page') IS NOT NULL;
"

# DWD层动作action日志加载脚本
dwd_action_log="
INSERT OVERWRITE TABLE ${APP}.dwd_action_log partition(dt = '${do_date}')
SELECT
    get_json_object(log, '$.common.ar') AS area_code,
    get_json_object(log, '$.common.ba') AS brand,
    get_json_object(log, '$.common.ch') AS channel,
    get_json_object(log, '$.common.is_new') AS is_new,
    get_json_object(log, '$.common.md') AS model,
    get_json_object(log, '$.common.mid') AS mid_id,
    get_json_object(log, '$.common.os') AS os,
    get_json_object(log, '$.common.uid') AS user_id,
    get_json_object(log, '$.common.vc') AS version_code,
    get_json_object(log, '$.page.during_time') AS during_time,
    get_json_object(log, '$.page.item') AS page_item,
    get_json_object(log, '$.page.item_type') AS page_item_type,
    get_json_object(log, '$.page.last_page_id') AS last_page_id,
    get_json_object(log, '$.page.page_id') AS page_id,
    get_json_object(log, '$.page.source_type') AS source_type,
    get_json_object(action, '$.action_id') AS action_id,
    get_json_object(action, '$.item') AS item,
    get_json_object(action, '$.item_type') AS item_type,
    get_json_object(action, '$.ts') AS ts
FROM ${APP}.ods_log
LATERAL VIEW explode_json_array(get_json_object(log, '$.actions')) tmp as action
WHERE dt='${do_date}'
  AND get_json_object(log, '$.actions') IS NOT NULL;
"

# DWD层曝光display日志加载脚本
dwd_display_log="
SET hive.stats.column.autogather = false ;
INSERT OVERWRITE TABLE ${APP}.dwd_display_log PARTITION(dt = '${do_date}')
SELECT
    get_json_object(log, '$.common.ar') AS area_code,
    get_json_object(log, '$.common.ba') AS brand,
    get_json_object(log, '$.common.ch') AS channel,
    get_json_object(log, '$.common.is_new') AS is_new,
    get_json_object(log, '$.common.md') AS model,
    get_json_object(log, '$.common.mid') AS mid_id,
    get_json_object(log, '$.common.os') AS os,
    get_json_object(log, '$.common.uid') AS user_id,
    get_json_object(log, '$.common.vc') AS version_code,
    get_json_object(log, '$.page.during_time') AS during_time,
    get_json_object(log, '$.page.item') AS page_item,
    get_json_object(log, '$.page.item_type') AS page_item_type,
    get_json_object(log, '$.page.last_page_id') AS last_page_id,
    get_json_object(log, '$.page.page_id') AS page_id,
    get_json_object(log, '$.page.source_type') AS source_type,
    get_json_object(log, '$.ts') AS ts,
    get_json_object(display, '$.display_type') AS display_type,
    get_json_object(display, '$.item') AS item,
    get_json_object(display, '$.item_type') AS item_type,
    get_json_object(display, '$.order') AS `order`,
    get_json_object(display, '$.pos_id') AS pos_id
FROM ${APP}.ods_log
LATERAL VIEW explode_json_array(get_json_object(log,'$.displays')) tmp AS display
WHERE dt='${do_date}'
  AND get_json_object(log, '$.displays') IS NOT NULL;
"

# DWD层错误error日志加载脚本
dwd_error_log="
SET hive.stats.column.autogather = false ;
INSERT OVERWRITE TABLE ${APP}.dwd_error_log partition(dt='${do_date}')
SELECT
    get_json_object(log, '$.common.ar') AS area_code,
    get_json_object(log, '$.common.ba') AS brand,
    get_json_object(log, '$.common.ch') AS channel,
    get_json_object(log, '$.common.is_new') AS is_new,
    get_json_object(log, '$.common.md') AS model,
    get_json_object(log, '$.common.mid') AS mid_id,
    get_json_object(log, '$.common.os') AS os,
    get_json_object(log, '$.common.uid') AS user_id,
    get_json_object(log, '$.common.vc') AS version_code,
    get_json_object(log, '$.page.item') AS page_item,
    get_json_object(log, '$.page.item_type') AS page_item_type,
    get_json_object(log, '$.page.last_page_id') AS last_page_id,
    get_json_object(log, '$.page.page_id') AS page_id,
    get_json_object(log, '$.page.source_type') AS source_type,
    get_json_object(log, '$.start.entry') AS entry,
    get_json_object(log, '$.start.loading_time') AS loading_time,
    get_json_object(log, '$.start.open_ad_id') AS open_ad_id,
    get_json_object(log, '$.start.open_ad_ms') AS open_ad_ms,
    get_json_object(log, '$.start.open_ad_skip_ms') AS open_ad_skip_ms,
    get_json_object(log, '$.actions') AS actions,
    get_json_object(log, '$.displays') AS displays,
    get_json_object(log, '$.ts') AS ts,
    get_json_object(log, '$.err.error_code') AS error_code,
    get_json_object(log, '$.err.msg') AS msg
FROM ${APP}.ods_log
WHERE dt = '${do_date}'
  AND get_json_object(log, '$.err') is not null;
"

case $1 in
    dwd_start_log )
        hive -e "${dwd_start_log}"
    ;;
    dwd_page_log )
        hive -e "${dwd_page_log}"
    ;;
    dwd_action_log )
        hive -e "${dwd_action_log}"
    ;;
    dwd_display_log )
        hive -e "${dwd_display_log}"
    ;;
    dwd_error_log )
        hive -e "${dwd_error_log}"
    ;;
    all )
        hive -e "${dwd_start_log}${dwd_page_log}${dwd_action_log}${dwd_display_log}${dwd_error_log}"
    ;;
esac


#
#step1.执行权限
# chmod 777 ods_to_dwd_log.sh
#step2.某张表，某天数据处理同步DWD层
# ods_to_dwd_log.sh dwd_start_log 2024-01-07
#step3.某张表，当天日期前一天数据处理同步DWD层
# ods_to_dwd_log.sh dwd_start_log
#step4.所有日志表，某天数据处理同步DWD层
# ods_to_dwd_log.sh all 2024-01-07
#

